/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package thesis.haipv.qa.persistence;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import thesis.haipv.qa.comtrust.ComTrust;
import thesis.haipv.qa.comtrust.Transaction;
import thesis.haipv.qa.model.Feedback;

/**
 *
 * @author PHANHAI
 */
public class FeedbackDAO {

    private DatabaseContext db;

    public FeedbackDAO() {
        db = DatabaseContext.getInstance();
    }

    /**
     * 
     * @param userId
     * @param partnerId
     * @param id_answer
     * @param trust
     * @param createtime 
     */
    public void createFeedback(String userId, String partnerId, String id_answer, float trust, long createtime) {
        int exist = checkFeedback(userId, id_answer);
        if (exist == -1) {
            insertFeedback(userId, id_answer, trust, createtime);
        } else if (exist != (int) trust) {
            updateFeedback(userId, id_answer, trust, createtime);
        } else {
            deleteFeedback(userId, id_answer);
        }
        // update trust
        ArrayList<Transaction> transactions = getFeedbacks(userId, partnerId);
        ExperienceTrustDAO edao = new ExperienceTrustDAO();
        float exptrust = edao.getExperienceTrust(userId, partnerId);
        if (exptrust == -1) {
            edao.createExperienceTrust(userId, partnerId, ComTrust.getExperienceTrust(transactions), createtime);
        } else {
            edao.updateExperienceTrust(userId, partnerId, ComTrust.getExperienceTrust(transactions), createtime);
        }
    }

    private ArrayList<Transaction> getFeedbacks(String userId, String partnerId) {
        ArrayList<Transaction> transaction = new ArrayList<>();
        String query = "SELECT f.Trust, f.CreateTime "
                + "FROM feedbacks f "
                + "JOIN answers a ON a.Id = f.AnswerId "
                + "WHERE f.AskerId = ? AND a.ResponderId = ? "
                + "ORDER BY f.CreateTime DESC";
        try {
            PreparedStatement ps = db.getConnection().prepareStatement(query);
            ps.setString(1, userId);
            ps.setString(2, partnerId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                transaction.add(new Transaction(rs.getFloat("Trust"), rs.getLong("f.CreateTime")));
            }
            ps.close();
        } catch (Exception e) {
        }
        return transaction;
    }

    /**
     *
     * @param reviewer
     * @param id_answer
     * @return -1: No exist, 0 untrust, 1 trust
     */
    private int checkFeedback(String userId, String answerId) {
        int exist = -1;
        String query = "SELECT Trust FROM feedbacks WHERE AskerId = ? AND AnswerId = ?";
        try {
            PreparedStatement ps = db.getConnection().prepareStatement(query);
            ps.setString(1, userId);
            ps.setString(2, answerId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                exist = (int) rs.getFloat("Trust");
            } else {
                exist = -1;
            }
            ps.close();
        } catch (Exception e) {
            exist = -1;
        }
        return exist;
    }

    private void deleteFeedback(String userId, String answerId) {
        String query = "DELETE FROM feedbacks WHERE AskerId = ? AND AnswerId = ?";
        try {
            PreparedStatement ps = db.getConnection().prepareStatement(query);
            ps.setString(1, userId);
            ps.setString(2, answerId);
            ps.executeUpdate();
            ps.close();
        } catch (Exception e) {
        }
    }

    private void insertFeedback(String userId, String answerId, float trust, long createtime) {
        String query = "INSERT INTO feedbacks (Trust, AskerId, AnswerId, CreateTime) "
                + "VALUES(?, ?, ?, ?)";
        try {
            PreparedStatement ps = db.getConnection().prepareStatement(query);
            ps.setFloat(1, trust);
            ps.setString(2, userId);
            ps.setString(3, answerId);
            ps.setLong(4, createtime);
            ps.executeUpdate();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void updateFeedback(String userId, String answerId, float trust, long createtime) {
        String query = "UPDATE feedbacks "
                + "SET Trust = ?, CreateTime = ? "
                + "WHERE AskerId = ? AND AnswerId = ?";
        try {
            PreparedStatement ps = db.getConnection().prepareStatement(query);
            ps.setFloat(1, trust);
            ps.setLong(2, createtime);
            ps.setString(3, userId);
            ps.setString(4, answerId);
            ps.executeUpdate();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
